CREATE view [dbo].[vGifts] as
  select min(Activity.ID)                              ID,
           min(Activity.TRANSACTION_DATE)  TRANSACTION_DATE,
           min(Activity.SOURCE_CODE)            SOURCE_CODE,
           -sum(Trans.AMOUNT)                       AMOUNT,
           max(Trans.TRANS_NUMBER)                      TRANS_NUMBER,
           min(Activity.CAMPAIGN_CODE)       CAMPAIGN_CODE,
           min(Activity.SOLICITOR_ID)            SOLICITOR_ID,
           sum (Activity.TAXABLE_VALUE)        TAXABLE_VALUE,
           max(Activity.OTHER_CODE)             OTHER_CODE,
           max(Activity.DESCRIPTION)             DESCRIPTION,     
           max(UF_1)                                         UF_1,     
           max(Trans.INSTALL_BILL_DATE)      INSTALL_BILL_DATE,
           max(Trans.OWNER_ORG_CODE)     OWNER_ORG_CODE,
           max(Activity.SEQN)                          SEQN,
           max(Trans.MERGE_CODE)               MERGE_CODE,
           MAX(Trans.Mem_Trib_Name_Text) Tributee,
           MAX(Activity.Mem_Trib_code) Mem_Trib_code,
           MAX(CONVERT(VARCHAR(512),Activity.NOTE_2)) AS NOTE_2,
           MAX(Trans_Notify.Notify_ID) Notify_ID      
    from Trans LEFT OUTER JOIN Activity on Trans.ACTIVITY_SEQN = Activity.SEQN
               LEFT OUTER JOIN Trans_Notify ON Trans_Notify.TRANS_NUMBER = Activity.ORIGINATING_TRANS_NUM
    where Trans.TRANSACTION_TYPE = 'DIST' and Trans.JOURNAL_TYPE = 'IN'
      and Trans.INSTALL_BILL_DATE is null
      and Trans.SOURCE_SYSTEM = 'FR'
group by Trans.TRANS_NUMBER



GO
GRANT REFERENCES ON  [dbo].[vGifts] TO [IMIS]
GRANT SELECT ON  [dbo].[vGifts] TO [IMIS]
GRANT INSERT ON  [dbo].[vGifts] TO [IMIS]
GRANT DELETE ON  [dbo].[vGifts] TO [IMIS]
GRANT UPDATE ON  [dbo].[vGifts] TO [IMIS]
GO